package sgc.beans;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.html.HtmlDataTable;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import sgc.struc.Material;

@ManagedBean(name = "materialBean")
@RequestScoped
public class MaterialBean implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3064903903936635192L;
	private DataSource ds;
	private Material material;
	private HtmlDataTable htmlDataTable;
	private int tabindex;

	public MaterialBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		material = new Material();
	}

	public Material getMaterial() {
		return material;
	}

	public void setMaterial(Material material) {
		this.material = material;
	}

	public HtmlDataTable getHtmlDataTable() {
		return htmlDataTable;
	}

	public int getTabindex() {
		return tabindex;
	}

	public void setHtmlDataTable(HtmlDataTable htmlDataTable) {
		this.htmlDataTable = htmlDataTable;
	}

	public void setTabindex(int tabindex) {
		this.tabindex = tabindex;
	}

	private int act_company() {
		int company;
		company = 0;
		try {
			FacesContext context = FacesContext.getCurrentInstance();
			CheckUserBean cu = (CheckUserBean) context.getApplication()
					.evaluateExpressionGet(context, "#{checkUserBean}",
							CheckUserBean.class);
			company = cu.getUsuario().getCompany_code();
		} catch (Exception e) {
		}
		return company;

	}

	public List<Material> getMaterialList() throws SQLException {

		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllMaterial_1(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, act_company());

		cs.execute();
		ResultSet result = cs.getResultSet();

		List<Material> list = new ArrayList<Material>();

		while (result.next()) {
			Material aux_material = new Material();
			aux_material.setCode(result.getInt(2));
			aux_material.setName(result.getString(3));
			aux_material.setDescrip(result.getString(4));
			aux_material.setMaterialclass(result.getString(5));
			aux_material.setUnit(result.getString(6));
			aux_material.setStock(result.getBoolean(7));
			list.add(aux_material);
		}
		con.close();

		return list;
	}

	public List<String> getNameList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllMaterial_1(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, act_company());
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<String> list = new ArrayList<String>();
		String name;
		while (result.next()) {
			name = result.getString(2) + " - " + result.getString(3);
			list.add(name);
		}
		con.close();
		return list;
	}

	public String edit() {
		material = (Material) htmlDataTable.getRowData();
		tabindex = 0;
		return "OK";
	}

	public boolean check(int p_code) throws SQLException {
		int aux_cod;
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetMaterial(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, p_code);
		cs.setInt(2, act_company());
		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		aux_cod = result.getInt(1);
		con.close();

		if (aux_cod != 0) {
			return true;
		} else {
			return false;
		}
	}

	public void save() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc;
		if (check(material.getCode()) == true) {
			simpleProc = "{ CALL sp_updMaterial(?,?,?,?,?,?,?) }";
		} else {
			simpleProc = "{ CALL sp_addMaterial(?,?,?,?,?,?,?) }";
		}

		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, material.getCode());
		cs.setString(2, material.getName());
		cs.setString(3, material.getDescrip());
		cs.setString(4, material.getMaterialclass());
		cs.setString(5, material.getUnit());
		cs.setBoolean(6, material.getStock());
		cs.setInt(7, act_company());

		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		String aux_message = result.getString(3);
		FacesContext.getCurrentInstance()
				.addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_INFO,
								aux_message, null));
		material = null;
		tabindex = 0;

	}
}
